using System;
using System.Data;

namespace QL_Truong_Hoc.DAO
{
    public sealed class Untils
    {
        //private static readonly string connectionString = ConfigurationManager.ConnectionStrings["System.Data.SqlClient"].ConnectionString;

        #region Global Excute Database

        /// <summary>
        ///
        /// Excute None Return Query
        /// </summary>
        /// <param name="Script">script value</param>
        /// <returns> error message if it's occur otherwise empty</returns>
        public static string ExecuteNonQuery(string Script)
        {
            try
            {
                //SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, Script);
                Program.gobjDb.ExecuteNonQuery(CommandType.Text, Script);
            }
            catch (Exception ex)
            {
                return ex.Message;
            };
            return string.Empty;
        }

        /// <summary>
        /// Excute None Return store procedure
        ///
        /// </summary>
        /// <param name="spName">procedure name</param>
        /// <param name="parameterValues">store procedure parameters</param>
        /// <returns>error message if it's occur otherwise empty</returns>
        public static string ExecuteNonQuery(string spName, params object[] parameterValues)
        {
            try
            {
                //SqlHelper.ExecuteNonQuery(connectionString, spName, parameterValues);
                Program.gobjDb.ExecuteNonQuery(spName, parameterValues);
            }
            catch (Exception ex)
            {
                return ex.Message;
            };
            return string.Empty;
        }

        /// <summary>
        /// Excute None Return store procedure
        ///
        /// </summary>
        /// <param name="spName">procedure name</param>
        /// <param name="parameterValues">store procedure parameters</param>
        /// <returns>error message if it's occur otherwise empty</returns>
        public static string ExecuteNonQuery(ref System.Data.SqlClient.SqlTransaction Transaction, string spName, params object[] parameterValues)
        {
            try
            {
                //SqlHelper.ExecuteNonQuery(connectionString, spName, parameterValues);
                Program.gobjDb.ExecuteNonQuery(Transaction, spName, parameterValues);
            }
            catch (Exception ex)
            {
                return ex.Message;
            };
            return string.Empty;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="sErr"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReaderEx(string SQL, ref string sErr)
        {
            sErr = string.Empty;
            try
            {
                //return ((IDataReader)(SqlHelper.ExecuteReader(connectionString, CommandType.Text, SQL)));
                return ((IDataReader)(Program.gobjDb.ExecuteReader(CommandType.Text, SQL)));
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        /// Run a return values query
        /// </summary>
        /// <param name="SQL">TSQL </param>
        /// <param name="sErr">error message if it's occur otherwise empty</param>
        /// <returns>Datata set contain return value </returns>
        public static DataSet ExecuteReader(string SQL, ref string sErr)
        {
            sErr = string.Empty;
            try
            {
                //return ConvertDataReaderToDataSet(((IDataReader)(SqlHelper.ExecuteReader(connectionString, CommandType.Text, SQL))));
                return ConvertDataReaderToDataSet(((IDataReader)(Program.gobjDb.ExecuteReader(CommandType.Text, SQL))));
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        /// Excute a store procedure
        /// </summary>
        /// <param name="spName">store procedure  name</param>
        /// <param name="sErr">error message if it's occur otherwise empty</param>
        /// <param name="parameterValues">store procedure parameters</param>
        /// <returns></returns>
        public static DataSet ExecuteReader(string spName, ref string sErr, params object[] parameterValues)
        {
            sErr = string.Empty;
            try
            {
                //return ConvertDataReaderToDataSet(((IDataReader)(SqlHelper.ExecuteReader(connectionString, spName, parameterValues))));
                return ConvertDataReaderToDataSet(((IDataReader)(Program.gobjDb.ExecuteReader(spName, parameterValues))));
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        /// return ireader
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="sErr"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReaderEx(string spName, ref string sErr, params object[] parameterValues)
        {
            sErr = string.Empty;
            try
            {
                //return ((IDataReader)(SqlHelper.ExecuteReader(connectionString, spName, parameterValues)));
                return ((IDataReader)(Program.gobjDb.ExecuteReader(spName, parameterValues)));
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        ///Excute a Scalar TSQL
        /// </summary>
        /// <param name="SQL">TSQL </param>
        /// <param name="sErr">error message if it's occur otherwise empty </param>
        /// <returns>Return vale as object type</returns>
        public static object ExecuteScalar(string SQL, ref string sErr)
        {
            sErr = string.Empty;
            try
            {
                //return SqlHelper.ExecuteScalar(connectionString, CommandType.Text, SQL);
                return Program.gobjDb.ExecuteScalar(CommandType.Text, SQL);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        /// Excute a Scalar store procedure
        /// </summary>
        /// <param name="spName">store procedure name</param>
        /// <param name="sErr">error message if it's occur otherwise empty</param>
        /// <param name="parameterValues">prammeters list/param>
        /// <returns></returns>
        public static object ExecuteScalar(string spName, ref string sErr, params object[] parameterValues)
        {
            sErr = string.Empty;

            try
            {
                //return SqlHelper.ExecuteScalar(connectionString, spName, parameterValues);
                return Program.gobjDb.ExecuteScalar(spName, parameterValues);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        /// <summary>
        /// Excute a Scalar store procedure
        /// </summary>
        /// <param name="spName">store procedure name</param>
        /// <param name="sErr">error message if it's occur otherwise empty</param>
        /// <param name="parameterValues">prammeters list/param>
        /// <returns></returns>
        public static object ExecuteScalar(ref System.Data.SqlClient.SqlTransaction Transaction, string spName, ref string sErr, params object[] parameterValues)
        {
            sErr = string.Empty;

            try
            {
                //return SqlHelper.ExecuteScalar(connectionString, spName, parameterValues);
                return Program.gobjDb.ExecuteScalar(Transaction, spName, parameterValues);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            return null;
        }

        //--------------------------------///
        /// <summary>
        /// Convert IDataReader to dataset
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static DataSet ConvertDataReaderToDataSet(IDataReader reader)
        {
            DataSet objDataSet = new DataSet();
            do
            {
                objDataSet.Tables.Add(ConvertDataReaderToDataTable(reader, false));
            } while (reader.NextResult());
            reader.Close();
            return objDataSet;
        }

        /// <summary>
        /// Convert IDataReader to Data table
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="bClose"></param>
        /// <returns></returns>
        public static DataTable ConvertDataReaderToDataTable(IDataReader reader, bool bClose)
        {
            DataTable objDataTable = new DataTable();

            int intFieldCount = reader.FieldCount;
            int intCounter;
            for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
            {
                objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
            }
            objDataTable.BeginLoadData();
            object[] objValues = new object[intFieldCount];
            while (reader.Read())
            {
                reader.GetValues(objValues);
                objDataTable.LoadDataRow(objValues, true);
            }
            if (bClose)
            {
                reader.Close();
            }
            objDataTable.EndLoadData();
            return objDataTable;
        }

        #endregion Global Excute Database

        #region " dataset processing"

        public static string StoreToDataSetXML(DataSet ds)
        {
            string ret = string.Empty;
            System.IO.StringWriter sw = new System.IO.StringWriter();
            ds.WriteXml(sw, XmlWriteMode.WriteSchema);
            ret = sw.ToString();
            sw.Close();
            return ret;
        }

        public static DataSet GetDataSetFromXML(string sxml)
        {
            DataSet ds = new DataSet();
            try
            {
                System.IO.StringReader sr = new System.IO.StringReader(sxml);
                ds.ReadXml(sr);
                sr.Close();
            }
            catch { }
            return ds;
        }

        #endregion " dataset processing"
    }
}